// Licensed to the Apache Software Foundation (ASF) under one
// or more contributor license agreements.  See the NOTICE file
// distributed with this work for additional information
// regarding copyright ownership.  The ASF licenses this file
// to you under the Apache License, Version 2.0 (the
// "License"); you may not use this file except in compliance
// with the License.  You may obtain a copy of the License at
//
//   http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing,
// software distributed under the License is distributed on an
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
// KIND, either express or implied.  See the License for the
// specific language governing permissions and limitations
// under the License.

package org.apache.doris.nereids.datasets.ssb;

import org.apache.doris.utframe.TestWithFeService;

public class SSBUtils {
    public static final String Q1_1 = "SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE\n"
            + "FROM lineorder, dates\n"
            + "WHERE\n"
            + "    lo_orderdate = d_datekey\n"
            + "    AND d_year = 1993\n"
            + "    AND lo_discount BETWEEN 1 AND 3\n"
            + "    AND lo_quantity < 25";

    public static final String Q1_2 = "SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE\n"
            + "FROM lineorder, dates\n"
            + "WHERE\n"
            + "    lo_orderdate = d_datekey\n"
            + "    AND d_yearmonth = 'Jan1994'\n"
            + "    AND lo_discount BETWEEN 4 AND 6\n"
            + "    AND lo_quantity BETWEEN 26 AND 35";

    public static final String Q1_3 = "SELECT\n"
            + "    SUM(lo_extendedprice * lo_discount) AS REVENUE\n"
            + "FROM lineorder, dates\n"
            + "WHERE\n"
            + "    lo_orderdate = d_datekey\n"
            + "    AND d_weeknuminyear = 6\n"
            + "    AND d_year = 1994\n"
            + "    AND lo_discount BETWEEN 5 AND 7\n"
            + "    AND lo_quantity BETWEEN 26 AND 35";

    public static final String Q2_1 = "SELECT SUM(lo_revenue), d_year, p_brand\n"
            + "FROM lineorder, dates, part, supplier\n"
            + "WHERE\n"
            + "    lo_orderdate = d_datekey\n"
            + "    AND lo_partkey = p_partkey\n"
            + "    AND lo_suppkey = s_suppkey\n"
            + "    AND p_category = 'MFGR#12'\n"
            + "    AND s_region = 'AMERICA'\n"
            + "GROUP BY d_year, p_brand\n"
            + "ORDER BY d_year, p_brand";

    public static final String Q2_2 = "SELECT SUM(lo_revenue), d_year, p_brand\n"
            + "FROM lineorder, dates, part, supplier\n"
            + "WHERE\n"
            + "    lo_orderdate = d_datekey\n"
            + "    AND lo_partkey = p_partkey\n"
            + "    AND lo_suppkey = s_suppkey\n"
            + "    AND p_brand BETWEEN 'MFGR#2221' AND 'MFGR#2228'\n"
            + "    AND s_region = 'ASIA'\n"
            + "GROUP BY d_year, p_brand\n"
            + "ORDER BY d_year, p_brand";

    public static final String Q2_3 = "SELECT SUM(lo_revenue), d_year, p_brand\n"
            + "FROM lineorder, dates, part, supplier\n"
            + "WHERE\n"
            + "    lo_orderdate = d_datekey\n"
            + "    AND lo_partkey = p_partkey\n"
            + "    AND lo_suppkey = s_suppkey\n"
            + "    AND p_brand = 'MFGR#2239'\n"
            + "    AND s_region = 'EUROPE'\n"
            + "GROUP BY d_year, p_brand\n"
            + "ORDER BY d_year, p_brand";

    public static final String Q3_1 = "SELECT\n"
            + "    c_nation,\n"
            + "    s_nation,\n"
            + "    d_year,\n"
            + "    SUM(lo_revenue) AS REVENUE\n"
            + "FROM lineorder, customer, supplier, dates\n"
            + "WHERE\n"
            + "    lo_custkey = c_custkey\n"
            + "    AND lo_suppkey = s_suppkey\n"
            + "    AND lo_orderdate = d_datekey\n"
            + "    AND c_region = 'ASIA'\n"
            + "    AND s_region = 'ASIA'\n"
            + "    AND d_year >= 1992\n"
            + "    AND d_year <= 1997\n"
            + "GROUP BY c_nation, s_nation, d_year\n"
            + "ORDER BY d_year ASC, REVENUE DESC";

    public static final String Q3_2 = "SELECT\n"
            + "    c_city,\n"
            + "    s_city,\n"
            + "    d_year,\n"
            + "    SUM(lo_revenue) AS REVENUE\n"
            + "FROM lineorder, customer , supplier, dates\n"
            + "WHERE\n"
            + "    lo_custkey = c_custkey\n"
            + "    AND lo_suppkey = s_suppkey\n"
            + "    AND lo_orderdate = d_datekey\n"
            + "    AND c_nation = 'UNITED STATES'\n"
            + "    AND s_nation = 'UNITED STATES'\n"
            + "    AND d_year >= 1992\n"
            + "    AND d_year <= 1997\n"
            + "GROUP BY c_city, s_city, d_year\n"
            + "ORDER BY d_year ASC, REVENUE DESC";

    public static final String Q3_3 = "SELECT\n"
            + "    c_city,\n"
            + "    s_city,\n"
            + "    d_year,\n"
            + "    SUM(lo_revenue) AS REVENUE\n"
            + "FROM lineorder, customer, supplier, dates\n"
            + "WHERE\n"
            + "    lo_custkey = c_custkey\n"
            + "    AND lo_suppkey = s_suppkey\n"
            + "    AND lo_orderdate = d_datekey\n"
            + "    AND (\n"
            + "        c_city = 'UNITED KI1'\n"
            + "        OR c_city = 'UNITED KI5'\n"
            + "    )\n"
            + "    AND (\n"
            + "        s_city = 'UNITED KI1'\n"
            + "        OR s_city = 'UNITED KI5'\n"
            + "    )\n"
            + "    AND d_year >= 1992\n"
            + "    AND d_year <= 1997\n"
            + "GROUP BY c_city, s_city, d_year\n"
            + "ORDER BY d_year ASC, REVENUE DESC";

    public static final String Q3_4 = "SELECT\n"
            + "    c_city,\n"
            + "    s_city,\n"
            + "    d_year,\n"
            + "    SUM(lo_revenue) AS REVENUE\n"
            + "FROM lineorder, customer, supplier, dates\n"
            + "WHERE\n"
            + "    lo_custkey = c_custkey\n"
            + "    AND lo_suppkey = s_suppkey\n"
            + "    AND lo_orderdate = d_datekey\n"
            + "    AND (\n"
            + "        c_city = 'UNITED KI1'\n"
            + "        OR c_city = 'UNITED KI5'\n"
            + "    )\n"
            + "    AND (\n"
            + "        s_city = 'UNITED KI1'\n"
            + "        OR s_city = 'UNITED KI5'\n"
            + "    )\n"
            + "    AND d_yearmonth = 'Dec1997'\n"
            + "GROUP BY c_city, s_city, d_year\n"
            + "ORDER BY d_year ASC, REVENUE DESC";

    public static final String Q4_1 = "SELECT\n"
            + "    d_year,\n"
            + "    c_nation,\n"
            + "    SUM(lo_revenue - lo_supplycost) AS PROFIT\n"
            + "FROM dates, customer, supplier, part, lineorder\n"
            + "WHERE\n"
            + "    lo_custkey = c_custkey\n"
            + "    AND lo_suppkey = s_suppkey\n"
            + "    AND lo_partkey = p_partkey\n"
            + "    AND lo_orderdate = d_datekey\n"
            + "    AND c_region = 'AMERICA'\n"
            + "    AND s_region = 'AMERICA'\n"
            + "    AND (\n"
            + "        p_mfgr = 'MFGR#1'\n"
            + "        OR p_mfgr = 'MFGR#2'\n"
            + "    )\n"
            + "GROUP BY d_year, c_nation\n"
            + "ORDER BY d_year, c_nation";

    public static final String Q4_2 = "SELECT\n"
            + "    d_year,\n"
            + "    s_nation,\n"
            + "    p_category,\n"
            + "    SUM(lo_revenue - lo_supplycost) AS PROFIT\n"
            + "FROM dates, customer, supplier, part, lineorder\n"
            + "WHERE\n"
            + "    lo_custkey = c_custkey\n"
            + "    AND lo_suppkey = s_suppkey\n"
            + "    AND lo_partkey = p_partkey\n"
            + "    AND lo_orderdate = d_datekey\n"
            + "    AND c_region = 'AMERICA'\n"
            + "    AND s_region = 'AMERICA'\n"
            + "    AND (\n"
            + "        d_year = 1997\n"
            + "        OR d_year = 1998\n"
            + "    )\n"
            + "    AND (\n"
            + "        p_mfgr = 'MFGR#1'\n"
            + "        OR p_mfgr = 'MFGR#2'\n"
            + "    )\n"
            + "GROUP BY d_year, s_nation, p_category\n"
            + "ORDER BY d_year, s_nation, p_category";

    public static final String Q4_3 = "SELECT\n"
            + "    d_year,\n"
            + "    s_city,\n"
            + "    p_brand,\n"
            + "    SUM(lo_revenue - lo_supplycost) AS PROFIT\n"
            + "FROM dates, customer, supplier, part, lineorder\n"
            + "WHERE\n"
            + "    lo_custkey = c_custkey\n"
            + "    AND lo_suppkey = s_suppkey\n"
            + "    AND lo_partkey = p_partkey\n"
            + "    AND lo_orderdate = d_datekey\n"
            + "    AND s_nation = 'UNITED STATES'\n"
            + "    AND (\n"
            + "        d_year = 1997\n"
            + "        OR d_year = 1998\n"
            + "    )\n"
            + "    AND p_category = 'MFGR#14'\n"
            + "GROUP BY d_year, s_city, p_brand\n"
            + "ORDER BY d_year, s_city, p_brand";

    public static void createTables(TestWithFeService service) throws Exception {
        service.createTable("CREATE TABLE IF NOT EXISTS `lineorder` (\n"
                + "  `lo_orderkey` int(11) NOT NULL COMMENT \"\",\n"
                + "  `lo_linenumber` int(11) NOT NULL COMMENT \"\",\n"
                + "  `lo_custkey` int(11) NOT NULL COMMENT \"\",\n"
                + "  `lo_partkey` int(11) NOT NULL COMMENT \"\",\n"
                + "  `lo_suppkey` int(11) NOT NULL COMMENT \"\",\n"
                + "  `lo_orderdate` int(11) NOT NULL COMMENT \"\",\n"
                + "  `lo_orderpriority` varchar(16) NOT NULL COMMENT \"\",\n"
                + "  `lo_shippriority` int(11) NOT NULL COMMENT \"\",\n"
                + "  `lo_quantity` int(11) NOT NULL COMMENT \"\",\n"
                + "  `lo_extendedprice` int(11) NOT NULL COMMENT \"\",\n"
                + "  `lo_ordtotalprice` int(11) NOT NULL COMMENT \"\",\n"
                + "  `lo_discount` int(11) NOT NULL COMMENT \"\",\n"
                + "  `lo_revenue` int(11) NOT NULL COMMENT \"\",\n"
                + "  `lo_supplycost` int(11) NOT NULL COMMENT \"\",\n"
                + "  `lo_tax` int(11) NOT NULL COMMENT \"\",\n"
                + "  `lo_commitdate` int(11) NOT NULL COMMENT \"\",\n"
                + "  `lo_shipmode` varchar(11) NOT NULL COMMENT \"\"\n"
                + ") ENGINE=OLAP\n"
                + "DUPLICATE KEY(`lo_orderkey`)\n"
                + "COMMENT \"OLAP\"\n"
                + "PARTITION BY RANGE(`lo_orderdate`)\n"
                + "(PARTITION p1 VALUES [(\"-2147483648\"), (\"19930101\")),\n"
                + "PARTITION p2 VALUES [(\"19930101\"), (\"19940101\")),\n"
                + "PARTITION p3 VALUES [(\"19940101\"), (\"19950101\")),\n"
                + "PARTITION p4 VALUES [(\"19950101\"), (\"19960101\")),\n"
                + "PARTITION p5 VALUES [(\"19960101\"), (\"19970101\")),\n"
                + "PARTITION p6 VALUES [(\"19970101\"), (\"19980101\")),\n"
                + "PARTITION p7 VALUES [(\"19980101\"), (\"19990101\")))\n"
                + "DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 48\n"
                + "PROPERTIES (\n"
                + "\"replication_num\" = \"1\",\n"
                + "\"colocate_with\" = \"groupa1\",\n"
                + "\"in_memory\" = \"false\",\n"
                + "\"storage_format\" = \"DEFAULT\"\n"
                + ")");

        service.createTable("CREATE TABLE IF NOT EXISTS `customer` (\n"
                + "  `c_custkey` int(11) NOT NULL COMMENT \"\",\n"
                + "  `c_name` varchar(26) NOT NULL COMMENT \"\",\n"
                + "  `c_address` varchar(41) NOT NULL COMMENT \"\",\n"
                + "  `c_city` varchar(11) NOT NULL COMMENT \"\",\n"
                + "  `c_nation` varchar(16) NOT NULL COMMENT \"\",\n"
                + "  `c_region` varchar(13) NOT NULL COMMENT \"\",\n"
                + "  `c_phone` varchar(16) NOT NULL COMMENT \"\",\n"
                + "  `c_mktsegment` varchar(11) NOT NULL COMMENT \"\"\n"
                + ") ENGINE=OLAP\n"
                + "DUPLICATE KEY(`c_custkey`)\n"
                + "COMMENT \"OLAP\"\n"
                + "DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 12\n"
                + "PROPERTIES (\n"
                + "\"replication_num\" = \"1\",\n"
                + "\"colocate_with\" = \"groupa2\",\n"
                + "\"in_memory\" = \"false\",\n"
                + "\"storage_format\" = \"DEFAULT\"\n"
                + ")");

        service.createTable("CREATE TABLE IF NOT EXISTS `dates` (\n"
                + "  `d_datekey` int(11) NOT NULL COMMENT \"\",\n"
                + "  `d_date` varchar(20) NOT NULL COMMENT \"\",\n"
                + "  `d_dayofweek` varchar(10) NOT NULL COMMENT \"\",\n"
                + "  `d_month` varchar(11) NOT NULL COMMENT \"\",\n"
                + "  `d_year` int(11) NOT NULL COMMENT \"\",\n"
                + "  `d_yearmonthnum` int(11) NOT NULL COMMENT \"\",\n"
                + "  `d_yearmonth` varchar(9) NOT NULL COMMENT \"\",\n"
                + "  `d_daynuminweek` int(11) NOT NULL COMMENT \"\",\n"
                + "  `d_daynuminmonth` int(11) NOT NULL COMMENT \"\",\n"
                + "  `d_daynuminyear` int(11) NOT NULL COMMENT \"\",\n"
                + "  `d_monthnuminyear` int(11) NOT NULL COMMENT \"\",\n"
                + "  `d_weeknuminyear` int(11) NOT NULL COMMENT \"\",\n"
                + "  `d_sellingseason` varchar(14) NOT NULL COMMENT \"\",\n"
                + "  `d_lastdayinweekfl` int(11) NOT NULL COMMENT \"\",\n"
                + "  `d_lastdayinmonthfl` int(11) NOT NULL COMMENT \"\",\n"
                + "  `d_holidayfl` int(11) NOT NULL COMMENT \"\",\n"
                + "  `d_weekdayfl` int(11) NOT NULL COMMENT \"\"\n"
                + ") ENGINE=OLAP\n"
                + "DUPLICATE KEY(`d_datekey`)\n"
                + "COMMENT \"OLAP\"\n"
                + "DISTRIBUTED BY HASH(`d_datekey`) BUCKETS 1\n"
                + "PROPERTIES (\n"
                + "\"replication_num\" = \"1\",\n"
                + "\"in_memory\" = \"false\",\n"
                + "\"colocate_with\" = \"groupa3\",\n"
                + "\"storage_format\" = \"DEFAULT\"\n"
                + ")");

        service.createTable("CREATE TABLE IF NOT EXISTS `supplier` (\n"
                + "  `s_suppkey` int(11) NOT NULL COMMENT \"\",\n"
                + "  `s_name` varchar(26) NOT NULL COMMENT \"\",\n"
                + "  `s_address` varchar(26) NOT NULL COMMENT \"\",\n"
                + "  `s_city` varchar(11) NOT NULL COMMENT \"\",\n"
                + "  `s_nation` varchar(16) NOT NULL COMMENT \"\",\n"
                + "  `s_region` varchar(13) NOT NULL COMMENT \"\",\n"
                + "  `s_phone` varchar(16) NOT NULL COMMENT \"\"\n"
                + ") ENGINE=OLAP\n"
                + "DUPLICATE KEY(`s_suppkey`)\n"
                + "COMMENT \"OLAP\"\n"
                + "DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 12\n"
                + "PROPERTIES (\n"
                + "\"replication_num\" = \"1\",\n"
                + "\"colocate_with\" = \"groupa4\",\n"
                + "\"in_memory\" = \"false\",\n"
                + "\"storage_format\" = \"DEFAULT\"\n"
                + ")");

        service.createTable("CREATE TABLE IF NOT EXISTS `part` (\n"
                + "  `p_partkey` int(11) NOT NULL COMMENT \"\",\n"
                + "  `p_name` varchar(23) NOT NULL COMMENT \"\",\n"
                + "  `p_mfgr` varchar(7) NOT NULL COMMENT \"\",\n"
                + "  `p_category` varchar(8) NOT NULL COMMENT \"\",\n"
                + "  `p_brand` varchar(10) NOT NULL COMMENT \"\",\n"
                + "  `p_color` varchar(12) NOT NULL COMMENT \"\",\n"
                + "  `p_type` varchar(26) NOT NULL COMMENT \"\",\n"
                + "  `p_size` int(11) NOT NULL COMMENT \"\",\n"
                + "  `p_container` varchar(11) NOT NULL COMMENT \"\"\n"
                + ") ENGINE=OLAP\n"
                + "DUPLICATE KEY(`p_partkey`)\n"
                + "COMMENT \"OLAP\"\n"
                + "DISTRIBUTED BY HASH(`p_partkey`) BUCKETS 12\n"
                + "PROPERTIES (\n"
                + "\"replication_num\" = \"1\",\n"
                + "\"colocate_with\" = \"groupa5\",\n"
                + "\"in_memory\" = \"false\",\n"
                + "\"storage_format\" = \"DEFAULT\"\n"
                + ")");
    }
}
